package com.F02.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Logger;

import com.F02.actionform.F020202ActionForm;
import com.F02.dao.F020202DAO;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;

/**
 * @leip
 * @标准产品明细维护
 * @数据库操作类
 * @方法命名规则：静态参数名称、例如：查询方法，方法名 QUERYLIST();
 * @数据库某字段如果的新增值，如果要取当前时间，请在数据库中表时间的字段上设置GETDATE(), @ insert语句执行时，会自动插入时间。避免系统时间于服务器时间不统一造成错误。 @ 例表c_customers的customer_date字段。

 */

public class F020202DAO extends DbOperationExt{
	
	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F020202DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F020202DAO() {
		super();
	}

	public F020202DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @查询
	 */
	public List QUERYLIST(F020202ActionForm actionForm,
			HttpServletRequest request) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();				
		Map map = null;
		ResultSet rest = null;
		
		msql.append(" SELECT b.id,e.code,e.phases, ");       
		msql.append(" b.material_code,d.[name], ");
		msql.append(" b.quantity,d.[description] ");			
		msql.append(" FROM t_standard_products a, ");
		msql.append(" t_standard_detail b, ");
		msql.append(" t_family c,t_materials d,t_critical_phases e ");
		msql.append(" WHERE a.code = b.standard_code ");
		msql.append(" and b.serial = c.serial ");
		msql.append(" and a.family_code = c.family_code ");
		msql.append(" and b.material_code =d.code ");
		msql.append(" and c.phases_code = e.code ");
		msql.append(" and a.code='"+actionForm.getCode()+"' ");
		msql.append(" order by b.serial ");
		try {
			log.info("QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());			
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("id", CommonTools.null2String(rest
						.getString("id")));
				map.put("phasesCode", CommonTools.null2String(rest
						.getString("code")));
				map.put("phasesName", CommonTools.null2String(rest
						.getString("phases")));
				map.put("materialCode", CommonTools.null2String(rest
						.getString("material_code")));
				map.put("materialName", CommonTools.null2String(rest
						.getString("name")));
				map.put("quantity", CommonTools.null2String(rest
						.getString("quantity")));
				map.put("description", CommonTools.null2String(rest
						.getString("description")));				
				list.add(map);
			}									
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
	/**
	 * @查询
	 */
	public List REFRESHLIST(F020202ActionForm actionForm,
			HttpServletRequest request) {
		StringBuffer msql = new StringBuffer();
		StringBuffer condition = new StringBuffer();;
		List list = new ArrayList();				
		Map map = null;
		ResultSet rest = null;
		/**产品类别*/
		if(actionForm.getPhasesCode()!=null&&!actionForm.getPhasesCode().equals("")){
			condition.append(" and b.serial = adyh.FUN_GETSerial('"+actionForm.getPhasesCode()+"') ");
		}
		msql.append(" SELECT b.id,e.code,e.phases, ");       
		msql.append(" b.material_code,d.[name], ");
		msql.append(" b.quantity,d.[description] ");			
		msql.append(" FROM t_standard_products a, ");
		msql.append(" t_standard_detail b, ");
		msql.append(" t_family c,t_materials d,t_critical_phases e ");
		msql.append(" WHERE a.code = b.standard_code ");
		msql.append(" and b.serial = c.serial ");
		msql.append(" and a.family_code = c.family_code ");
		msql.append(" and b.material_code =d.code ");
		msql.append(" and c.phases_code = e.code ");
		msql.append(" and a.code='"+actionForm.getCode()+"' ");
		msql.append(" "+condition.toString());
		msql.append(" order by b.serial ");
		try {
			log.info("QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());						
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("id", CommonTools.null2String(rest
						.getString("id")));
				map.put("phasesCode", CommonTools.null2String(rest
						.getString("code")));
				map.put("phasesName", CommonTools.null2String(rest
						.getString("phases")));
				map.put("materialCode", CommonTools.null2String(rest
						.getString("material_code")));
				map.put("materialName", CommonTools.null2String(rest
						.getString("name")));
				map.put("quantity", CommonTools.null2String(rest
						.getString("quantity")));
				map.put("description", CommonTools.null2String(rest
						.getString("description")));										
				
				list.add(map);
			}									
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
	/**
	 * @查询
	 */
	public List QUERYBOMLIST(F020202ActionForm actionForm,
			HttpServletRequest request) {
		StringBuffer msql = new StringBuffer();
		StringBuffer condition = new StringBuffer();
		List list = new ArrayList();				
		Map map = null;
		ResultSet rest = null;
			condition.append(" and a.product_type = '"+actionForm.getProductType()+"' ");
		if(actionForm.getManufacturingOrder()!=null&&!actionForm.getManufacturingOrder().equals("")){
			condition.append(" and a.code = '"+actionForm.getManufacturingOrder()+"' ");
		}
		if(actionForm.getProductCode()!=null&&!actionForm.getProductCode().equals("")){
			condition.append(" and a.product_code = '"+actionForm.getProductCode()+"' ");
		}
		if(actionForm.getFamilyCode()!=null&&!actionForm.getFamilyCode().equals("")){
			condition.append(" and a.family_code = '"+actionForm.getFamilyCode()+"' ");
		}
		log.info(condition.toString());
		
		msql.append(" SELECT b.id,e.code,e.phases, ");       
		msql.append(" b.material_code,d.[name], ");
		msql.append(" b.quantity,d.[description] ");			
		msql.append(" FROM t_standard_products a, ");
		msql.append(" t_standard_detail b, ");
		msql.append(" t_family c,t_materials d,t_critical_phases e ");
		msql.append(" WHERE a.code = b.standard_code ");
		msql.append(" and b.serial = c.serial ");
		msql.append(" and a.family_code = c.family_code ");
		msql.append(" and b.material_code =d.code ");
		msql.append(" and c.phases_code = e.code ");
		msql.append(" "+condition.toString());
		msql.append(" order by b.serial ");
		try {
			log.info("QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());						
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("id", CommonTools.null2String(rest
						.getString("id")));
				map.put("phasesCode", CommonTools.null2String(rest
						.getString("code")));
				map.put("phasesName", CommonTools.null2String(rest
						.getString("phases")));
				map.put("materialCode", CommonTools.null2String(rest
						.getString("material_code")));
				map.put("materialName", CommonTools.null2String(rest
						.getString("name")));
				map.put("quantity", CommonTools.null2String(rest
						.getString("quantity")));
				map.put("description", CommonTools.null2String(rest
						.getString("description")));										
				
				list.add(map);
			}									
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
	/**
	 * @编辑保存
	 */
	public boolean SAVE_UPDATE(F020202ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo) throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		StringBuffer msql = new StringBuffer();
		msql.append(" UPDATE t_standard_detail ");
		msql.append(" SET quantity="+actionForm.getQuantity());
		msql.append(" WHERE standard_code = '"+  actionForm.getCode() + "' ");
		msql.append(" and id = '"+  actionForm.getId() + "' ");
		try {
			log.info("SAVE_UPDATE sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");

		} catch (Exception e) {
			actionForm.setErrorFlag("1");
			e.printStackTrace();
			super.rollback();

		}
		super.commit();
		return flag;
	}
	/**
	 * @编辑保存
	 */
	public boolean UPDATE_COST(F020202ActionForm actionForm) throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		
		StringBuffer msql = new StringBuffer();
		msql.append(" UPDATE t_standard_products ");
		msql.append(" SET material_cost= adyh.FUN_GETCOSTSTAND("+actionForm.getCode()+")");
		msql.append(" WHERE code = "+  actionForm.getCode() + " ");		
		try {
			log.info("UPDATE sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);			

		} catch (Exception e) {			
			e.printStackTrace();
			super.rollback();

		}
		super.commit();
		return flag;
	}
	/**
	 * @删除
	 */
	public boolean DELETE(F020202ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo, HttpServletRequest request)
			throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);		
		StringBuffer msql = new StringBuffer();
		msql.append(" DELETE FROM t_standard_detail ");
		msql.append(" WHERE id = " + actionForm.getId().trim()+" ");		
		
		try {
			log.info("Delete sql is " + msql.toString());			
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");
		} catch (Exception e) {
			e.printStackTrace();
			super.rollback();
			actionForm.setErrorFlag("1");
		}
		super.commit();
		return flag;
	}	
	/**
	 * @新增保存
	 * @要记录操作，在插入后要向操作记录表也插一条语句，也就是用执行多天SQL， @ 所有用super.executeUpdate(sqlList,true)执行
	 */
	public boolean SAVE_INSERT(F020202ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo) {
		boolean falg = false;
		List sqlList = new ArrayList();
		/** 存放SQL的LIST */
		StringBuffer yhczsql = new StringBuffer();
		/** 操作记录SQL */
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		/** 获得操作记录SQL */
		StringBuffer msql = new StringBuffer();
		msql.append("INSERT INTO t_standard_detail( ");		
		msql.append("standard_code, ");
		msql.append("serial,material_code, ");
		msql.append("quantity,unit_price ");		
		msql.append(" )VALUES ( ");				
		msql.append("'" + CommonTools.null2String(actionForm.getCode()) + "', ");
		msql.append("adyh.FUN_GETSerial('" + actionForm.getPhasesCode() + "'), ");
		msql.append("'" + CommonTools.null2String(actionForm.getMaterialCode()) + "', ");
		msql.append("" + CommonTools.null2String(actionForm.getQuantity()) + ", ");
		msql.append("adyh.FUN_GETMATERIALCOST('" + CommonTools.null2String(actionForm.getMaterialCode()) + "') )");

		try {
			log.info("SAVE_INSERT sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			falg = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");/**成功*/
		} catch (SQLException ex) {
			if ("1".equals(String.valueOf(ex.getErrorCode()))) {
				/** ex.getErrorCode()返回一，表示主键冲突 */
				actionForm.setErrorFlag("1");/** 主键冲突! */
			} else {
				actionForm.setErrorFlag("1");/**保存失败*/
			}
		} catch (Exception e) {
			actionForm.setErrorFlag("4");/**  */
			e.printStackTrace();
			super.rollback();

		}
		super.commit();

		return falg;
	}
}
